﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace Sklad1_2.Data
{
    class Helper
    {
        Helper()
        {

        }
        public static string ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;data source=" + Application.StartupPath + "\\Sklad.mdb";

        public static string QuerySklad = "select id,description as [Склад] from angar";

        public static string QueryCategory = "select a.id,b.id,b.description as [Склад],a.description as [Категория]  from category a,angar b where b.id=a.sklad_id";

        public static string QueryFirma = "select a.id,b.id,c.id,b.description as [Склад],c.description as [Категория],a.firm_name as [Фирма] from angar b,category c,firma a where b.id=a.sklad_id and c.id=a.category_id";

        public static string QueryProduct = "select a.id,b.id,c.id,d.id,a.description as [Склад],b.description as [Категория],c.firm_name as [Фирма],d.product_name as [Наименование],d.price as [Цена],d.count1 as [Количество в упаковке] from angar a,category b,firma c,product d where a.id=d.sklad_id and b.id=d.category_id and c.id=d.firma_id";

        public static string QueryMain = "select f.id,e.id,a.id,b.id,c.id,d.id,e.description as [Клиент],a.description as [Склад],b.description as [Категория],c.firm_name as [Фирма],d.product_name as [Товар],f.count1 as [Кол-во],f.count2 as [Кол-во1],f.flag as [В упаковках],f.summa as [Сумма],f.datec as [Дата покупки],f.raschet as [Расчет],f.ostatok as [Остаток] from angar a,category b,firma c,product d,customer e,main f where f.sklad_id=a.id and f.category_id=b.id and f.firm_id=c.id and f.product_id=d.id and f.customer_id=e.id";

        public static string QueryVozvrat = "select b.description as ФИО,d.product_name as Товар,c.datec as [Дата заказа],a.count1 as Количество,a.summa as Сумма,a.datec1 as [Дата возврата] from vozvrat a,customer b,main c,product d where a.main_id=c.id and c.customer_id=b.id and c.product_id=d.id";

        public static string QueryCustomer = "select id,description as ФИО,organization as Организация,address as Адрес,telephone as Телефон from customer where id=id";

        public static string QueryZakupka =
"select a.id,a.sklad_id,a.category_id,a.firma_id,a.product_id,b.description as [Склад],c.description as [Категория],d.firm_name as [Фирма],e.product_name as [Наименование товара],a.count1 as [Кол-во в упак],a.count2 as [Кол-во в шт],a.summa as [Сумма],a.datec as [Дата закупки],a.ostatok as [Остаток] from sklad a,angar b,category c,firma d,product e where a.sklad_id=b.id and a.category_id=c.id and a.firma_id=d.id and a.product_id=e.id";

        public static string QueryRas =
            "select c.description as [Клиент],d.product_name as [Товар],b.count2 as [Кол-во],b.summa as [Сумма],b.datec as [Дата],a.ras as [Расчет],a.ostatok as [Остаток],a.datec as [Дата расчета]  from ras a,main b,customer c,product d where a.main_id=b.id and b.customer_id=c.id and b.product_id=d.id";

        public static string QueryVozvrat1 = "select d.product_name as [Наименование товара],a.count1 as [Кол-во],a.datec1 as [Дата возврата],a.summa as [Сумма] from vozvrat1 a,sklad b,product d where b.product_id=d.id and a.zak_id=b.id";

        public static string QueryRas1 =
    "select d.product_name as [Товар],b.count2 as [Кол-во],b.summa as [Сумма],b.datec as [Дата закупки],a.ras as [Расчет],a.ostatok as [Остаток],a.datec as [Дата расчета] from ras1 a,sklad b,product d where a.zak_id=b.id and b.product_id=d.id";


        public static void Hidden(DataGridView aView,params int[] a)
        {
            foreach (var i in a)
            {
                aView.Columns[i].Visible = false;
            }
        }
        public static void Db2List(DataGridView aView, DataGridViewCellEventArgs e, ref List<string> aList, params int[] a)
        {
            foreach (var i in a)
            {
                if (e.RowIndex==-1)
                    continue;
                aList.Add(Convert.ToString(aView[i, e.RowIndex].Value));
            }

        }
    }
}
